import pymysql
from utils import constants

class DbUtil:

    def __init__(self):
        # 初始化数据库
        self.host = constants.DB_HOST
        self.user = constants.DB_ROOT
        self.pwd = constants.DB_PWD
        self.db = constants.DB_DATABASE
        self.connection = pymysql.connect(
            host=self.host,  # 数据库主机地址
            user=self.user,  # 数据库用户名
            password=self.pwd,  # 数据库密码
            database=self.db  # 要连接的数据库
        )

        # 创建游标对象
        self.cursor = self.connection.cursor()

    # 查询表中数据
    def find_list(self,table):
        # query = 'SELECT * FROM {}'.format(table)
        query = 'SELECT * FROM %s '%(table)
        self.cursor.execute(query)
        return self.cursor.fetchall()

    # 单个查询

    def find_by_name(self,table,where,where_value):
        # query = 'SELECT * FROM {} WHERE {}=%s'.format(table, where)
        query = 'SELECT * FROM %s WHERE %s=%%s' % (table, where)
        self.cursor.execute(query, (where_value))
        self.connection.commit()
        return self.cursor.fetchall()

    # 查询表中某列的信息
    def find_column(self,column,table):
        query = 'SELECT %s FROM %s ' %(column,table)
        self.cursor.execute(query)
        self.connection.commit()
        return self.cursor.fetchall()
    def find_oder_by_desc(self,table,where):
        query='SELECT * FROM %s order by %s desc'%(table,where)
        self.cursor.execute(query)
        self.connection.commit()
        return self.cursor.fetchall()

    def find_oder_by_asc(self,table,where):
        query='SELECT * FROM %s order by %s asc'%(table,where)
        self.cursor.execute(query)
        self.connection.commit()
        return self.cursor.fetchall()

    # 关闭数据库和游标
    def close(self):
        self.connection.close()
        self.cursor.close()

# 创建Dbuti类的实例
db_util=DbUtil()

# 查询表信息
# print(db_util.find_list('pms_role'))

# 查询表条件
# print(db_util.find_by_name('pms_role','remark','游客角色'))

# 查询表中某列的信息
# print(db_util.find_column('remark','pms_role'))
# print(db_util.find_column('user_name','rp_user_info'))

# 按照某个字段降序排序
# print(db_util.find_oder_by_desc('rp_user_info','create_time'))

# 按照某个字段升序排序
# print(db_util.find_oder_by_asc('rp_user_info','create_time'))